
package com.companymodule.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import util.ConnectDB;
import util.UtilDAO;
import util.VskPage;

import com.companymodule.bean.TbCompanyProductTypeMiddle;
import com.productmodule.bean.TbProductType;
import com.productmodule.dao.TbProductTypeDAO;

public class TbCompanyProductTypeMiddleDAO {
    private Connection conn = null;
    private PreparedStatement pt = null;
    private ResultSet rs = null;
    private boolean flag = false;
    private String sql = "";
    private TbCompanyProductTypeMiddle tbCompanyProductTypeMiddle=null;
    private List<TbCompanyProductTypeMiddle> tbCompanyProductTypeMiddleList=null;

    public boolean add(TbCompanyProductTypeMiddle inTbCompanyProductTypeMiddle){
        try{
        conn=ConnectDB.getSqlServerConnection();
        sql="insert into Tb_CompanyProductTypeMiddle(CompanyId,ProductTypeId) values(?,?)";
        pt = conn.prepareStatement(sql);
        pt.setInt(1,inTbCompanyProductTypeMiddle.getCompanyId());
        pt.setInt(2,inTbCompanyProductTypeMiddle.getProductTypeId());
        if(!new TbCompanyProductTypeMiddleDAO().isExistsTbCompanyProductTypeMiddle(inTbCompanyProductTypeMiddle))
        if (pt.executeUpdate() > 0) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return flag;
    }
    public boolean del(int inCompanyProductTypeMiddleId){
        return UtilDAO.del("Tb_CompanyProductTypeMiddle", "CompanyProductTypeMiddleId", inCompanyProductTypeMiddleId);
    }
    public boolean edit(TbCompanyProductTypeMiddle inTbCompanyProductTypeMiddle){
        try{
        conn=ConnectDB.getSqlServerConnection();
        sql="update Tb_CompanyProductTypeMiddle set CompanyId=?,ProductTypeId=?,CreateTime=getdate() where CompanyProductTypeMiddleId=?";
        pt = conn.prepareStatement(sql);
        pt.setInt(1,inTbCompanyProductTypeMiddle.getCompanyId());
        pt.setInt(2,inTbCompanyProductTypeMiddle.getProductTypeId());
        pt.setInt(3,inTbCompanyProductTypeMiddle.getCompanyProductTypeMiddleId());
        if (pt.executeUpdate() > 0) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return flag;
    }
    public TbCompanyProductTypeMiddle getTbCompanyProductTypeMiddleByCompanyProductTypeMiddleId(int inCompanyProductTypeMiddleId) {
        tbCompanyProductTypeMiddle=null;
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_CompanyProductTypeMiddle where CompanyProductTypeMiddleId=? ";
            pt = conn.prepareStatement(sql);
            pt.setInt(1,inCompanyProductTypeMiddleId);
            rs = pt.executeQuery();
            while (rs.next()) {
                tbCompanyProductTypeMiddle = getTbCompanyProductTypeMiddle(rs);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbCompanyProductTypeMiddle;
    }
    public List<TbCompanyProductTypeMiddle> getTbCompanyProductTypeMiddleAll() {
        tbCompanyProductTypeMiddleList=new ArrayList<TbCompanyProductTypeMiddle>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_CompanyProductTypeMiddle";
            pt = conn.prepareStatement(sql);
            rs = pt.executeQuery();
            while (rs.next()) {
                tbCompanyProductTypeMiddleList.add(getTbCompanyProductTypeMiddle(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbCompanyProductTypeMiddleList;
    }

    public List<TbCompanyProductTypeMiddle> getTbCompanyProductTypeMiddleAll(VskPage vp) {
        tbCompanyProductTypeMiddleList=new ArrayList<TbCompanyProductTypeMiddle>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_CompanyProductTypeMiddle";
            vp.setAllCountSql(sql);
            vp.getAllRecordCount(conn, vp.getAllCountSql());
            pt = conn.prepareStatement(vp.processPageSql( "Tb_CompanyProductTypeMiddle", "CompanyProductTypeMiddleId"));
            rs = pt.executeQuery();
            while (rs.next()) {
                tbCompanyProductTypeMiddleList.add(getTbCompanyProductTypeMiddle(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbCompanyProductTypeMiddleList;
    }
    public List<TbCompanyProductTypeMiddle> getTbCompanyProductTypeMiddleByCompanyId(int companyId) {
    	  tbCompanyProductTypeMiddleList=new ArrayList<TbCompanyProductTypeMiddle>();
          try{
              conn = ConnectDB.getSqlServerConnection();
              sql = "select * from Tb_CompanyProductTypeMiddle where CompanyId="+companyId;
              pt = conn.prepareStatement(sql);
              rs = pt.executeQuery();
              while (rs.next()) {
            		  tbCompanyProductTypeMiddleList.add(getTbCompanyProductTypeMiddle(rs));
              }
          }catch(Exception e){
              e.printStackTrace();
          }finally {
                          ConnectDB.closeSqlConnection();        }
          return tbCompanyProductTypeMiddleList;
    }
    public List<TbCompanyProductTypeMiddle> getTbCompanyProductTypeMiddleByProductTypeId(int productTypeId,int companyId) {
    	tbCompanyProductTypeMiddleList=new ArrayList<TbCompanyProductTypeMiddle>();
    	try{
    		conn = ConnectDB.getSqlServerConnection();
    		sql = "select * from Tb_CompanyProductTypeMiddle where ProductTypeId="+productTypeId+" and CompanyId="+companyId;
    		pt = conn.prepareStatement(sql);
    		rs = pt.executeQuery();
    		while (rs.next()) {
    			tbCompanyProductTypeMiddleList.add(getTbCompanyProductTypeMiddle(rs));
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}finally {
    		ConnectDB.closeSqlConnection();        }
    	return tbCompanyProductTypeMiddleList;
    }
    /**
     * 根据公司ID及产品类型级别得到其下面的所有类型
     *@author qingyu zhang
     *@function:
     * @param isMaxProductType
     * @param companyId
     * @return
     *2011-7-1
     *下午05:24:50
     *ProjectManagerT
     *com.companymodule.dao
     *List<TbCompanyProductTypeMiddle>
     */
    public List<TbCompanyProductTypeMiddle> getProductTypeMiddleByIsMaxAndCompanyId(int isMaxProductType,int companyId) {
    	tbCompanyProductTypeMiddleList=new ArrayList<TbCompanyProductTypeMiddle>();
    	try{
    		conn = ConnectDB.getSqlServerConnection();
    		sql = "select * from  Tb_CompanyProductTypeMiddle where ProductTypeId in(select ProductTypeId from Tb_ProductType where IsMaxProductType=?) and CompanyId=?";
    		pt = conn.prepareStatement(sql);
    		pt.setInt(1, isMaxProductType);
    		pt.setInt(2, companyId);
    		rs = pt.executeQuery();
    		while (rs.next()) {
    				tbCompanyProductTypeMiddleList.add(getTbCompanyProductTypeMiddle(rs));
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}finally {
    		ConnectDB.closeSqlConnection();        }
    	return tbCompanyProductTypeMiddleList;
    }
    public boolean isExistsTbCompanyProductTypeMiddle(TbCompanyProductTypeMiddle tbCompanyProductTypeMiddle) {
        try{
        	flag=false;
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_CompanyProductTypeMiddle where CompanyId=? and  ProductTypeId=?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1,tbCompanyProductTypeMiddle.getCompanyId());
            pt.setInt(2, tbCompanyProductTypeMiddle.getProductTypeId());
            rs = pt.executeQuery();
            if (rs.next()) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
    		ConnectDB.closeSqlConnection();        }
        return flag;
    }
    public TbCompanyProductTypeMiddle getTbCompanyProductTypeMiddle(ResultSet rs) throws Exception{
        TbCompanyProductTypeMiddle tbCompanyProductTypeMiddle = new TbCompanyProductTypeMiddle();
        tbCompanyProductTypeMiddle.setCompanyProductTypeMiddleId(rs.getInt("CompanyProductTypeMiddleId"));
        tbCompanyProductTypeMiddle.setCompanyId(rs.getInt("CompanyId"));
        tbCompanyProductTypeMiddle.setProductTypeId(rs.getInt("ProductTypeId"));
        tbCompanyProductTypeMiddle.setCreateTime(rs.getString("CreateTime"));
        return tbCompanyProductTypeMiddle;
    }
    public static void main(String[] args) {
		List<TbCompanyProductTypeMiddle> tptlist=new TbCompanyProductTypeMiddleDAO().getTbCompanyProductTypeMiddleByCompanyId(1);
		TbProductTypeDAO dao=new TbProductTypeDAO();
		String mstr="";
		for(TbCompanyProductTypeMiddle tpt:tptlist){
			List<TbProductType> ptlist=dao.getTbProductTypeStringUitlTopl(tpt.getTbProductType());
			for(TbProductType tp:ptlist){
				String t=tp.getProductTypeId()+":"+tp.getParentTypeId()+":"+tp.getProductTypeName();
				mstr="".equals(mstr)?tp.getProductTypeId()+":"+tp.getParentTypeId()+":"+tp.getProductTypeName():mstr+","+tp.getProductTypeId()+":"+tp.getParentTypeId()+":"+tp.getProductTypeName();
			}
		}
		System.out.println(mstr);
    	
	}
}
